package com.company.dao;

import com.company.config.DbConfig;
import com.company.model.Stu_Course;
import com.company.model.Student;
import com.company.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Stu_CourseDao {
    public boolean delete(int selected_id) {
        Connection conn=null;
        PreparedStatement prst=null;
        ResultSet rs = null;
        try{
            conn= DbConfig.getConn();
            String sql = "delete from student_course where id=?";
            prst = conn.prepareStatement(sql);
            prst.setInt(1, selected_id);
            if(prst.executeUpdate() > 0)
                return true;

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DbConfig.close(conn,prst,rs);
        }

        return false;
    }

    public boolean isSelected(Stu_Course sc) {
        Connection conn=null;
        PreparedStatement prst=null;
        ResultSet rs = null;
        String sql = "select * from student_course where student_Id=? and course_Id=?";
        try {
            conn=DbConfig.getConn();
            prst= conn.prepareStatement(sql);
            prst.setInt(1, sc.getStudent_ID());
            prst.setInt(2, sc.getCourse_ID());
            rs= prst.executeQuery();
            if(rs.next()){
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConfig.close(conn,prst,rs);
        }
        return false;
    }

    public boolean updateSelectedCourse(Stu_Course sc) {
        Connection conn=null;
        PreparedStatement prst=null;
        ResultSet rs = null;
        String sql = "update student_course set student_Id = ?,course_Id = ? where id = ?";
        try {
            conn=DbConfig.getConn();
            prst= conn.prepareStatement(sql);
            prst.setInt(1, sc.getStudent_ID());
            prst.setInt(2, sc.getCourse_ID());
            prst.setInt(3, sc.getId());
            if(prst.executeUpdate() > 0)return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConfig.close(conn,prst,rs);
        }
        return false;

    }

    public boolean addSelectedCourse(Stu_Course sc) {
        Connection conn=null;
        PreparedStatement stat=null;
        ResultSet rs = null;
        String sql = "insert into student_course values(null,?,?)";
        try {
            conn=DbConfig.getConn();
            stat = conn.prepareStatement(sql);
            stat.setInt(1, sc.getStudent_ID());
            stat.setInt(2, sc.getCourse_ID());
            if(stat.executeUpdate() > 0)return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally {
            DbConfig.close(conn,stat,rs);
        }
        return false;
    }

    public List<Stu_Course> getSelectedCourseList(Stu_Course selectedCourse) {
        Connection conn=null;
        PreparedStatement stat=null;
        ResultSet rs = null;
        List<Stu_Course> stu_courses=new ArrayList<>();

        try{
            conn= DbConfig.getConn();
            StringBuffer sqlString = new StringBuffer("select * from student_course");
            if(selectedCourse.getStudent_ID()!=0){
                sqlString.append(" and student_Id like '%"+selectedCourse.getStudent_ID()+"%'");
            }
            if(selectedCourse.getCourse_ID() != 0){
                sqlString.append(" and course_Id ="+selectedCourse.getCourse_ID());
            }
            stat = conn.prepareStatement(sqlString.toString().replaceFirst("and","where"));
            rs=stat.executeQuery();

            while(rs.next()){
                Stu_Course sc = new Stu_Course();
                sc.setId(rs.getInt("id"));
                sc.setStudent_ID(rs.getInt("student_Id"));
                sc.setCourse_ID(rs.getInt("course_Id"));
                stu_courses.add(sc);
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DbConfig.close(conn,stat,rs);
        }

        return stu_courses;
    }
}
